#include "home.h"
#include "ui_home.h"
#include <QDebug>
#include <QVector>
#include <QMessageBox>
#include <QToolBar>
#include <QAction>
#include <QMenu>
#include <QInputDialog>
#include <QByteArray>
#include <QDesktopServices>
#include <QUrl>
#include <ActiveQt/QAxObject>
#include <QFileDialog>
#include <QDateTime>

home::home(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::home)
{
    ui->setupUi(this);

    ui->tableWidget->setRowCount(20);
    ui->tableWidget->setColumnCount(20);
    list.resize(ui->tableWidget->rowCount());
    for (int i = 0; i < list.size(); i++) {
        list[i].resize(ui->tableWidget->columnCount());
    }

    QToolBar* tool_bar;

    tool_bar = addToolBar ( tr ( "Edit" ) );

    QAction* create;   // 新建按钮
    create = new QAction(QIcon(":/icon/xinjian.png"), "新建", this);
    create->setShortcut(Qt::Key_Control | Qt::Key_E);
    tool_bar->addAction(create);
    connect(create, SIGNAL(triggered()), this, SLOT(on_create_clicked()));

    QAction* save;     // 保存按钮
    save = new QAction(QIcon(":/icon/baocun.png"), "保存", this);
    save->setShortcut(Qt::Key_Control | Qt::Key_E);
    save->setToolTip("This is a tooltip");
    tool_bar->addAction(save);
    connect(save, SIGNAL(triggered()), this, SLOT(on_preserve_clicked()));

    QAction* open;     // 打开按钮
    open = new QAction(QIcon(":/icon/dakai.png"), "打开", this);
    open->setShortcut(Qt::Key_Control | Qt::Key_E);
    open->setToolTip("This is a tooltip");
    tool_bar->addAction(open);
    connect(open, SIGNAL(triggered()), this, SLOT(on_read_clicked()));

    QAction* copy;     // 复制按钮
    copy = new QAction(QIcon(":/icon/fuzhi.png"), "复制", this);
    copy->setShortcut(Qt::Key_Control | Qt::Key_E);
    tool_bar->addAction(copy);
    connect(copy, SIGNAL(triggered()), this, SLOT(on_copy_clicked()));

    QAction* paste;    // 粘贴按钮
    paste = new QAction(QIcon(":/icon/niantie.png"), "粘贴", this);
    QMenu* menu_paste;     // 选择性粘贴的菜单
    menu_paste = new QMenu(this);
    paste->setMenu(menu_paste);
    QAction* replace = new QAction("替换", this);
    menu_paste->addAction(replace);
    QAction* add = new QAction("相加", this);
    menu_paste->addAction(add);
    QAction* minus = new QAction("相减", this);
    menu_paste->addAction(minus);
    QAction* multiple = new QAction("相乘", this);
    menu_paste->addAction(multiple);
    QAction* divide = new QAction("相除", this);
    menu_paste->addAction(divide);
    QAction* transpose = new QAction("转置", this);
    menu_paste->addAction(transpose);
    tool_bar->addAction(paste);
    connect(replace, SIGNAL(triggered()), this, SLOT(on_replace_clicked()));
    connect(add, SIGNAL(triggered()), this, SLOT(on_add_clicked()));
    connect(minus, SIGNAL(triggered()), this, SLOT(on_minus_clicked()));
    connect(multiple, SIGNAL(triggered()), this, SLOT(on_multiple_clicked()));
    connect(divide, SIGNAL(triggered()), this, SLOT(on_divide_clicked()));
    connect(transpose, SIGNAL(triggered()), this, SLOT(on_transpose_clicked()));

    QAction* merge;     // 合并按钮和菜单
    merge = new QAction(QIcon(":/icon/hebing.png"), "合并单元格", this);
    QMenu* menu_merge = new QMenu(this);
    merge->setMenu(menu_merge);
    QAction* merge1 = new QAction(QIcon(":/icon/hebing.png"), "合并单元格", this);
    menu_merge->addAction(merge1);
    QAction* merge_cancel = new QAction(QIcon(":/icon/quxiaohebing.png"), "取消合并单元格", this);
    menu_merge->addAction(merge_cancel);
    tool_bar->addAction(merge);
//    connect(merge, SIGNAL(triggered()), this, SLOT(on_merge_clicked()));
    connect(merge1, SIGNAL(triggered()), this, SLOT(on_merge_clicked()));
    connect(merge_cancel, SIGNAL(triggered()), this, SLOT(on_cancel_merge_clicked()));

    // 插入按钮
    QAction* insert = new QAction(QIcon(":/icon/charuhang.png"), "插入", this);
    QMenu* menu_insert = new QMenu(this);
    insert->setMenu(menu_insert);
    QAction* insert_row = new QAction(QIcon(":/icon/charuhang.png"), "插入行", this);
    menu_insert->addAction(insert_row);
    QAction* insert_column = new QAction(QIcon(":/icon/charulie.png"), "插入列", this);
    menu_insert->addAction(insert_column);
    tool_bar->addAction(insert);
    connect(insert_row, SIGNAL(triggered()), this, SLOT(on_addrow_clicked()));
    connect(insert_column, SIGNAL(triggered()), this, SLOT(on_addcol_clicked()));

    // 删除按钮
    QAction* remove = new QAction(QIcon(":/icon/shanchuhang.png"), "删除", this);
    QMenu* menu_remove = new QMenu(this);
    remove->setMenu(menu_remove);
    QAction* remove_row = new QAction(QIcon(":/icon/shanchuhang.png"), "删除行", this);
    menu_remove->addAction(remove_row);
    QAction* remove_column = new QAction(QIcon(":/icon/shanchulie.png"), "删除列", this);
    menu_remove->addAction(remove_column);
    tool_bar->addAction(remove);
    connect(remove_row, SIGNAL(triggered()), this, SLOT(on_removerow_clicked()));
    connect(remove_column, SIGNAL(triggered()), this, SLOT(on_removecol_clicked()));

    // 高亮按钮
    QAction* highlight = new QAction(QIcon(":/icon/gaoliang.png"), "高亮", this);
    QMenu* menu_highlight = new QMenu(this);
    highlight->setMenu(menu_highlight);
    QAction* highlight1 = new QAction(QIcon(":/icon/gaoliang.png"), "高亮", this);
    menu_highlight->addAction(highlight1);
    QAction* highlight_cancel = new QAction(QIcon(":/icon/quxiaogaoliang.png"), "取消高亮", this);
    menu_highlight->addAction(highlight_cancel);
    tool_bar->addAction(highlight);
    connect(highlight, SIGNAL(triggered()), this, SLOT(on_highlight_clicked()));
    connect(highlight1, SIGNAL(triggered()), this, SLOT(on_highlight_clicked()));
    connect(highlight_cancel, SIGNAL(triggered()), this, SLOT(on_cancel_highlight_clicked()));

    QAction* sum;      // 合计按钮
    sum = new QAction(QIcon(":/icon/sum.png"), "合计", this);
    // 菜单部分
    QMenu* menu_operate;  // 运算的菜单
    menu_operate = new QMenu(this);
    sum->setMenu(menu_operate);
    QAction* sum1 = new QAction(QIcon(":/icon/sum.png"), "合计", this);
    menu_operate->addAction(sum1);
    QAction* ave = new QAction("平均值", this);
    menu_operate->addAction(ave);
    QAction* max = new QAction("最大值", this);
    menu_operate->addAction(max);
    QAction* min = new QAction("最小值", this);
    menu_operate->addAction(min);
    tool_bar->addAction(sum);
    connect(sum1, SIGNAL(triggered()), this, SLOT(on_sum_clicked()));
    connect(ave, SIGNAL(triggered()), this, SLOT(on_ave_clicked()));
    connect(max, SIGNAL(triggered()), this, SLOT(on_max_clicked()));
    connect(min, SIGNAL(triggered()), this, SLOT(on_min_clicked()));

    QAction* draw;     // 画图按钮
    draw = new QAction(QIcon(":/icon/zhexiantu.png"), "画图", this);
    draw->setShortcut(Qt::Key_Control | Qt::Key_E);
    tool_bar->addAction(draw);
    connect(draw, SIGNAL(triggered()), this, SLOT(on_draw_clicked()));

    QAction* formula;     // 公式按钮
    formula = new QAction(QIcon(":/icon/gongshi.png"), "公式", this);
    draw->setShortcut(Qt::Key_Control | Qt::Key_E);
    tool_bar->addAction(formula);
    connect(formula, SIGNAL(triggered()), this, SLOT(on_count_clicked()));

    QAction* sort;    // 排序按钮
    sort = new QAction(QIcon(":/icon/paixu.png"), "排序", this);
    QMenu* menu_sort = new QMenu(this);
    sort->setMenu(menu_sort);
    QAction* sort_ascend = new QAction(QIcon(":/icon/wenbenshengxu.png"), "升序", this);
    menu_sort->addAction(sort_ascend);
    QAction* sort_descend = new QAction(QIcon(":/icon/wenbenjiangxu.png"), "降序", this);
    menu_sort->addAction(sort_descend);
    tool_bar->addAction(sort);
    connect(sort_ascend, SIGNAL(triggered()), this, SLOT(on_ascend_clicked()));
    connect(sort_descend, SIGNAL(triggered()), this, SLOT(on_descend_clicked()));

    QAction* case_sensitive;
    case_sensitive = new QAction(QIcon(":/icon/qufendaxiaoxie.png"), "区分大小写", this);
    QMenu* menu_case = new QMenu(this);
    case_sensitive->setMenu(menu_case);
    QAction* case_sensitive1 = new QAction(QIcon(":/icon/qufendaxiaoxie.png"), "区分大小写", this);
    menu_case->addAction(case_sensitive1);
    QAction* case_insensitive = new QAction(QIcon(":/icon/hulvedaxiaoxie.png"), "忽略大小写", this);
    menu_case->addAction(case_insensitive);
    tool_bar->addAction(case_sensitive);
    connect(case_sensitive1, SIGNAL(triggered()), this, SLOT(on_check_clicked()));
    connect(case_insensitive, SIGNAL(triggered()), this, SLOT(on_check_clicked()));

    tool_bar->setToolButtonStyle(Qt::ToolButtonTextUnderIcon);

    tool_bar->setIconSize(QSize(60,60));


}

home::~home()
{
    delete ui;
}

void home::on_create_clicked() {
    home* h = new home;
    h->show();
}

void home::on_replace_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    // 代替
    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
            ui->tableWidget->setItem(i, j, new QTableWidgetItem(list[(i-top)%row_length][(j-left)%column_length]));
        }
    }
}


// 加法
void home::on_add_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;

    double num, sum;

    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
            if (ui->tableWidget->item(i, j) == NULL ||
                    (ui->tableWidget->item(i, j) && ui->tableWidget->item(i, j)->text() == "")) {
                num = 0;
            } else {
                num = ui->tableWidget->item(i, j)->text().toDouble();
            }

            sum = num + list[(i-top)%row_length][(j-left)%column_length].toDouble();
            ui->tableWidget->setItem(i, j, new QTableWidgetItem(QString::number(sum)));
        }
    }
}

// 除法
void home::on_divide_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;

    double num, sum;

    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
            if (ui->tableWidget->item(i, j) == NULL ||
                    (ui->tableWidget->item(i, j) && ui->tableWidget->item(i, j)->text() == "")) {
                num = 0;
            } else {
                num = ui->tableWidget->item(i, j)->text().toDouble();
            }

            if (list[(i-top)%row_length][(j-left)%column_length] == "") {
                QMessageBox::about(this, "提示", "除数里面有0捏~这样是不阔以用除法功能的呢亲~");
                continue;
            } else {
                sum = num / list[(i-top)%row_length][(j-left)%column_length].toDouble();
                ui->tableWidget->setItem(i, j, new QTableWidgetItem(QString::number(sum)));
            }
        }
    }
}


// 乘法
void home::on_multiple_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;

    double num, sum;

    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
            if (ui->tableWidget->item(i, j) == NULL ||
                    (ui->tableWidget->item(i, j) && ui->tableWidget->item(i, j)->text() == "")) {
                num = 0;
            } else {
                num = ui->tableWidget->item(i, j)->text().toDouble();
            }

            sum = num * list[(i-top)%row_length][(j-left)%column_length].toDouble();
            ui->tableWidget->setItem(i, j, new QTableWidgetItem(QString::number(sum)));
        }
    }
}

void home::on_minus_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;

    double num, sum;

    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
            if (ui->tableWidget->item(i, j) == NULL ||
                    (ui->tableWidget->item(i, j) && ui->tableWidget->item(i, j)->text() == "")) {
                num = 0;
            } else {
                num = ui->tableWidget->item(i, j)->text().toDouble();
            }

            sum = num - list[(i-top)%row_length][(j-left)%column_length].toDouble();
            ui->tableWidget->setItem(i, j, new QTableWidgetItem(QString::number(sum)));
        }
    }
}


void home::on_copy_clicked()
{
//    QList<QTableWidgetItem*> selectItems = ui->tableWidget->selectedItems();
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    row_length = bottom - top + 1;
    column_length = right - left + 1;

    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
            if (ui->tableWidget->item(i, j) == NULL ||
                    (ui->tableWidget->item(i, j) && ui->tableWidget->item(i, j)->text() == "")) {
                list[i-top][j-left] = "";
            } else {
                list[i-top][j-left] = ui->tableWidget->item(i, j)->text();
            }
        }
    }
    for (int i = 0; i < row_length; i++) {
        for (int j = 0; j < column_length; j++) {
            qDebug() << list[i][j].toInt() << ' ';
        }
    }
}

void home::on_transpose_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;

    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
            ui->tableWidget->setItem(i, j, new QTableWidgetItem(list[j-left][i-top]));
        }
    }
}

// 求和
void home::on_sum_clicked()
{
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;

    double sum = 0;

    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
            if (ui->tableWidget->item(i, j) == NULL ||
                    (ui->tableWidget->item(i, j) && ui->tableWidget->item(i, j)->text() == "")) {
                continue;
            } else {
                sum += ui->tableWidget->item(i, j)->text().toDouble();
            }
        }
    }
    ui->textEdit->setText(QString::number(sum));

}

void home::on_merge_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    ui->tableWidget->setSpan(top, left, bottom-top+1, right-left+1);
}

void home::on_count_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int column = range.leftColumn();
    int row = range.topRow();
    qDebug() <<row<<column;
    QString str = ui->tableWidget->item(row,column)->text();//取出字符串
    qDebug()<<str;
    int input1,input2;
    qreal result;
    char ope;
    char*  ch;
    QByteArray ba = str.toLatin1(); // must
    ch=ba.data();
    qDebug()<<ch;
    int row1=(ch[1]-48)*10+(ch[2]-48);
    int col1=(ch[3]-48)*10+(ch[4]-48);
    int row2=(ch[6]-48)*10+(ch[7]-48);
    int col2=(ch[8]-48)*10+(ch[9]-48);
    qDebug()<<row1<<col1<<row2<<col2;
    input1=ui->tableWidget->item(row1, col1)->text().toInt();
    input2=ui->tableWidget->item(row2, col2)->text().toInt();
    qDebug()<<ui->tableWidget->item(row1, col1)->text();
    qDebug()<<ui->tableWidget->item(row2, col2)->text();
    ope=ch[5];
    if(ope=='+')result=qreal(input1)+qreal(input2);
    if(ope=='-')result=qreal(input1)-qreal(input2);
    if(ope=='*')result=qreal(input1)*qreal(input2);
    if(ope=='/'){
        qDebug()<<input2;
        if(input2==0)QMessageBox::about(this, "提示", "除数里面有0捏~这样是不阔以用除法功能的呢亲~");
        else result=qreal(input1)/qreal(input2);

    }
    qDebug()<<result;
    ui->tableWidget->setItem(row, column, new QTableWidgetItem(QString::number(result)));

}

void home::on_highlight_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {

            ui->tableWidget->item(i,j)->setBackground(QColor(0,60,10));
            ui->tableWidget->item(i,j)->setForeground(QColor(200,111,100));
        }
    }
}

void home::on_draw_clicked() {
    //DataTable dataTable;
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    int k=0;
    DataList dataList;
    qreal yValue(0);
    for (int i = top; i <= bottom; i++) {
        yValue =ui->tableWidget->item(i, right)->text().toInt();
        qreal j=ui->tableWidget->item(i, left)->text().toInt();
        QPointF value(j,yValue);
        QString label = "Slice " + QString::number(1) + ":" + QString::number(k);
        k++;
        dataList << Data(value, label);
        }
    dataTable << dataList;
    int arrayheng[20];
    for(int k=0,i=top;i<=bottom;k++,i++)
    {
        arrayheng[k]=ui->tableWidget->item(i, left)->text().toInt();
    }
    maxheng=-1000;minheng=1000;
    for(int k=0;k<=bottom-top;k++)
    {;
        if(maxheng<arrayheng[k])
            maxheng=arrayheng[k];
        if(minheng>arrayheng[k])
            minheng=arrayheng[k];
    }
    int arrayzong[20];
    for(int k=0,i=top;i<=bottom;k++,i++)
    {
        arrayzong[k]=ui->tableWidget->item(i, right)->text().toInt();
    }
    maxzong=-1000;minzong=1000;
    for(int k=0;k<=bottom-top;k++)
    {
        if(maxzong<arrayzong[k])
            maxzong=arrayzong[k];
        if(minzong>arrayzong[k])
            minzong=arrayzong[k];
    }
    graph *configWindow = new graph(this);
    configWindow->getData();
    configWindow->getmaxh();
    configWindow->getminh();
    configWindow->getmaxz();
    configWindow->getminz();
    configWindow->show();
}

void home::on_cancel_highlight_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {

            ui->tableWidget->item(i,j)->setBackground(QColor(255,255,255));
            ui->tableWidget->item(i,j)->setForeground(QColor(0,0,0));
        }
    }
}

void home::on_cancel_merge_clicked() {
    ui->tableWidget->clearSpans();
}

void home::on_read_clicked() {
    QString path = QFileDialog::getOpenFileName(this,"open",
                                                             "../","execl(*.xlsx)");
                   //指定父对象（this），“open”具体操作，打开，“../”默认，之后可以添加要打开文件的格式
        QAxObject* cell;
        QString exceldata[100][100];
                   if(path.isEmpty()==false)
                   {
                       //文件对象
                       QFile file(path);
                       //打开文件,默认为utf8变量，
                       bool flag = file.open(QIODevice::ReadOnly);
                       if(flag == true)//打开成功
                       {
                           QAxObject *excel = new QAxObject(this);//建立excel操作对象
                           excel->setControl("Excel.Application");//连接Excel控件
                           excel->setProperty("Visible", false);//不显示窗体看效果
                           excel->setProperty("DisplayAlerts", false);//不显示警告看效果
                           /*********获取COM文件的一种方式************/
                           QAxObject *workbooks = excel->querySubObject("WorkBooks");
                           //获取工作簿(excel文件)集合
                           workbooks->dynamicCall("Open(const QString&)", path);//path至关重要，获取excel文件的路径
                           //打开一个excel文件
                           QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
                           QAxObject *worksheet = workbook->querySubObject("WorkSheets(int)",1);//访问excel中的工作表中第一个单元格
                           QAxObject *usedRange = worksheet->querySubObject("UsedRange");//eet的范围
                           /*********获取COM文件的一种方式************/
                           //获取打开excel的起始行数和列数和总共的行数和列数
                           int intRowStart = usedRange->property("Row").toInt();//起始行数
                           int intColStart = usedRange->property("Column").toInt(); //起始列数
                           QAxObject *rows, *columns;
                           rows = usedRange->querySubObject("Rows");//行
                           columns = usedRange->querySubObject("Columns");//列
                           int intRow = rows->property("Count").toInt();//行数
                           int intCol = columns->property("Count").toInt();//列数
                           //起始行列号
                           qDebug()<<intRowStart;
                           qDebug()<<intColStart;
                           //行数和列数
                           qDebug()<<intRow;
                           qDebug()<<intCol;
                           int a,b;
                           a=intRow-intRowStart+1,b=intCol-intColStart+1;
                           QByteArray text[a][b];
                           int coerow=0,coecol=0;
                           for (int i = intRowStart; i < intRowStart + intRow; i++,coerow++)
                               {
                                   coecol=0;//务必是要恢复初值的
                                   for (int j = intColStart; j < intColStart + intCol; j++,coecol++)
                                   {
                                       cell = excel->querySubObject("Cells(Int, Int)", i, j );
                                       qDebug()<<i<<j;
                                       QVariant cellValue = cell->dynamicCall("value");
                                       qDebug()<<i<<j;
                                       text[coerow][coecol]=cellValue.toByteArray();//QVariant转换为QByteArray
                                       qDebug()<<i<<j;
                                       if(QString(text[coerow][coecol])==nullptr)
                                           exceldata[coerow][coecol]=" ";
                                       else if(QString(text[coerow][coecol]).isEmpty())
                                            exceldata[coerow][coecol]=" ";
                                       else
                                           exceldata[coerow][coecol]=QString(text[coerow][coecol]);//QByteArray转换为QString
                                   }
                               }
                           for(int i=intRowStart;i<coerow+intRowStart;i++)
                                   for(int q=intColStart;q<coecol+intColStart;q++)
                                        ui->tableWidget->setItem(i-1, q-1, new QTableWidgetItem(exceldata[i-intRowStart][q-intColStart]));
                           workbook->dynamicCall( "Close(Boolean)", false );
                           excel->dynamicCall( "Quit(void)" );
                           delete excel;
                           QMessageBox::warning(this,tr("读取情况"),tr("读取完成！"),QMessageBox::Yes);
                       }
                       file.close();
                   }
}

void home::on_preserve_clicked() {
    QString filePath = QFileDialog::getSaveFileName(this, "Save Data", "untitle","Microsoft Excel 2013(.xlsx)");
        if (!filePath.isEmpty())
        {
        QAxObject* excel = new QAxObject(this);
        excel->setControl("ket.Application");//wps
        //excel->setControl("Excel.Application") //Excel
        excel->dynamicCall("SetVisible(bool Visible)", false);
        excel->setProperty("DisplayAlerts", false);
        QAxObject* workbooks = excel->querySubObject("WorkBooks");
        workbooks->dynamicCall("Add");
        QAxObject* workbook = excel->querySubObject("ActiveWorkBook");
        QAxObject* worksheets = workbook->querySubObject("Sheets");
        QAxObject* worksheet = worksheets->querySubObject("Item(int)", 1);
        int rowCount = ui->tableWidget->rowCount();
        int columnCount = ui->tableWidget->columnCount();
        for (int i = 0; i < rowCount ; ++i)
        {
        for (int j = 0; j < columnCount ; ++j)
        {

        QAxObject* Range = worksheet->querySubObject("Cells(int,int)", i+1, j+1);

        if(ui->tableWidget->item(i,j)==nullptr)
            Range->dynamicCall("SetValue(const QString &)", " ");

        else if(ui->tableWidget->item(i,j)->text().isEmpty())
             Range->dynamicCall("SetValue(const QString &)", " ");
        else
            Range->dynamicCall("SetValue(const QString &)", ui->tableWidget->item(i , j)->text());
        }
        workbook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(filePath));
        }
        if (excel != NULL)
        {
        excel->dynamicCall("Quit()");
        delete excel;
        excel = NULL;
        }
        QMessageBox::information(this, QStringLiteral("提示"), "Exporting data successful");
        }
}

void home::on_removerow_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    for(int i=bottom;i>=top;i--){
    int rowIndex = i;
    if (rowIndex != -1)
    {
    ui->tableWidget->removeRow(rowIndex);
    }
    }
}

void home::on_removecol_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    for(int i=right;i>=left;i--){
    int colIndex = i;
    if (colIndex != -1)
    {
    ui->tableWidget->removeColumn(colIndex);
    }
    }
}

void home::on_addrow_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    int row = ui->tableWidget->rowCount();
    ui->tableWidget->insertRow(row);//总行数增加1
    for(int i=ui->tableWidget->rowCount()-1;i>bottom;i--)
        for(int j=0;j<ui->tableWidget->columnCount();j++)
        {
            if(ui->tableWidget->item(i-1,j)==nullptr)
                ui->tableWidget->setItem(i, j, new QTableWidgetItem(""));
            else if(ui->tableWidget->item(i-1,j)->text().isEmpty())
                 ui->tableWidget->setItem(i, j, new QTableWidgetItem(""));
            else
                 ui->tableWidget->setItem(i, j, new QTableWidgetItem(ui->tableWidget->item(i-1,j)->text()));
        }
    for(int j=0;j<ui->tableWidget->columnCount();j++)
         ui->tableWidget->setItem(bottom+1, j, new QTableWidgetItem(""));
}

void home::on_addcol_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
            top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    int col = ui->tableWidget->columnCount();
    ui->tableWidget->insertColumn(col);//总列数增加1
    for(int i=ui->tableWidget->columnCount()-1;i>right;i--)
        for(int j=0;j<ui->tableWidget->rowCount();j++)
        {
            if(ui->tableWidget->item(j,i-1)==nullptr)
                ui->tableWidget->setItem(j, i, new QTableWidgetItem(""));
            else if(ui->tableWidget->item(j,i-1)->text().isEmpty())
                 ui->tableWidget->setItem(j, i, new QTableWidgetItem(""));
            else
                 ui->tableWidget->setItem(j, i, new QTableWidgetItem(ui->tableWidget->item(j,i-1)->text()));
        }
    for(int j=0;j<ui->tableWidget->rowCount();j++)
         ui->tableWidget->setItem(j, right+1, new QTableWidgetItem(""));
}

void home::on_ave_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
        top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    QList<QTableWidgetItem*> selectItems = ui->tableWidget->selectedItems();
    double sum=0;
    double ave;
    int num = 0;
    for (auto item :selectItems){
        if (item ==NULL){
            continue;
        }
        else{
            sum += item->text().toDouble();
            num++;
        }
    }
    ave = sum / num;
    ui->textEdit->setText(QString::number(ave));
}

void home::on_max_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
        top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    int max = ui->tableWidget->item(top,left)->text().toInt();
    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
        if (max < ui->tableWidget->item(i,j)->text().toInt()){
        max = ui->tableWidget->item(i,j)->text().toInt();
        }
        ui->textEdit->setText(QString::number(max));
        }
    }
}

void home::on_min_clicked() {
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    // 获取选择的表格区域的范围
    qDebug() << "selectedRanges("<<selectRanges.size()<<")----------";
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
        top = range.topRow(), bottom = range.bottomRow();
    qDebug() << left << right << top << bottom;
    int min=ui->tableWidget->item(top,left)->text().toInt();
    for (int i = top; i <= bottom; i++) {
        for (int j = left; j <= right; j++) {
        if( min > ui->tableWidget->item(i,j)->text().toInt()){
        min = ui->tableWidget->item(i,j)->text().toInt();
        }
    }
    ui->textEdit->setText(QString::number(min));
  }
}

void home::swap_letter(string A[], int arr[], int l, int r) {
    int b = arr[l];
    arr[l] = arr[r];
    arr[r] = b;
    string a = A[l];
    A[l] = A[r];
    A[r] = a;
}

void home::swap_num(int A[], int l, int r) {
    int a = A[l];
    A[l] = A[r];
    A[r] = a;
}

int home::compare(string s1, string s2) {
    int i = 0;
    int size1 = s1.size(), size2 = s2.size();
    while ((i < size1) && (i < size2)) {
        if (int(s1[i]) > int(s2[i])) {
            return 1;
        } else if (int(s1[i]) < int(s2[i])) {
            return -1;
        } else if (int(s1[i]) == int(s2[i])) {
            i++;
        }
    }
    if (size1 == size2) {
        return 0;
    } else if (i == size1) {
        return -1;
    } else if(i == size2) {
        return 1;
    }
    return 0;
}

void home::bubsort_num(int A[], int n) {
    for(int i = 0; i < n - 1; i++) {
          for(int j = n - 1; j > i; j--) {
             if(A[j] < A[j-1]) {
                swap_num(A, j, j-1);
             }
          }
       }
}

void home::bubsort_letter(string A[], int arr[], int n) {
    for(int i = 0; i < n - 1; i++) {
          for(int j = n - 1; j > i; j--) {
             if(compare(A[j], A[j-1]) == -1) {
                swap_letter(A, arr, j, j-1);
             }
          }
       }
}

void home::on_check_clicked() {
    flag++;
}

void home::on_ascend_clicked() {
    QList<QTableWidgetItem*> selectItems = ui->tableWidget->selectedItems();
    QStringList list;
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
        top = range.topRow(), bottom = range.bottomRow();
    for (auto item : selectItems) {
        list.append(item->text());
    }
    string* strlist = new string[list.size()];
    for (int i = 0; i < list.size(); i++) {
        strlist[i] = list[i].toStdString();
    }
    qDebug() << flag;
    if (48 <= int(strlist[0][0]) && int(strlist[0][0] <= 57)) {
        int* arr = new int[list.size()];
        for (int i = 0; i < list.size(); i++) {
            arr[i] = list[i].toInt();
        }
        // 对纯数字进行排序
        bubsort_num(arr, list.size());
        int k = 0;
        for (int i = top; i <= bottom; i++) {
            for (int j = left; j <= right; j++) {
                ui->tableWidget->setItem(i, j, new QTableWidgetItem(QString::number(arr[k++])));
            }
        }
    } else {
        // 对纯英文字母排序
        string* str = new string[list.size()];
        int* arr = new int[list.size()];
        for (int i = 0; i < list.size(); i++) {
            str[i] = strlist[i];
            arr[i] = i;
        }
        if (!(flag % 2)) {
            // 没选大小写敏感
            for (int i = 0; i < list.size(); i++) {
                transform(strlist[i].begin(),strlist[i].end(),strlist[i].begin(),::tolower);
            }
        }
        for (int i = 0; i < list.size(); i++) {
            qDebug() << QString::fromStdString(strlist[i]);
        }
        bubsort_letter(strlist, arr, list.size());
        int k = 0;
        for (int i = top; i <= bottom; i++) {
            for (int j = left; j <= right; j++) {
//                qDebug() << QString::fromStdString(strlist[k]);
                ui->tableWidget->setItem(i, j, new QTableWidgetItem(QString::fromStdString(str[arr[k++]])));
            }
        }
    }
}


void home::on_descend_clicked() {
    QList<QTableWidgetItem*> selectItems = ui->tableWidget->selectedItems();
    QStringList list;
    QList<QTableWidgetSelectionRange> selectRanges = this->ui->tableWidget->selectedRanges();
    QTableWidgetSelectionRange range = selectRanges[0];
    int left = range.leftColumn(), right = range.rightColumn(),
        top = range.topRow(), bottom = range.bottomRow();
    for (auto item : selectItems) {
        list.append(item->text());
    }
    string* strlist = new string[list.size()];
    for (int i = 0; i < list.size(); i++) {
        strlist[i] = list[i].toStdString();
    }
    qDebug() << flag;
    if (48 <= int(strlist[0][0]) && int(strlist[0][0] <= 57)) {
        int* arr = new int[list.size()];
        for (int i = 0; i < list.size(); i++) {
            arr[i] = list[i].toInt();
        }
        // 对纯数字进行排序
        bubsort_num(arr, list.size());
        int k = 0;
        for (int i = top; i <= bottom; i++) {
            for (int j = left; j <= right; j++) {
                ui->tableWidget->setItem(i, j, new QTableWidgetItem(QString::number(arr[k++])));
            }
        }
    } else {
        // 对纯英文字母排序
        string* str = new string[list.size()];
        int* arr = new int[list.size()];
        for (int i = 0; i < list.size(); i++) {
            str[i] = strlist[i];
            arr[i] = i;
        }
        if (!(flag % 2)) {
            // 没选大小写敏感
            for (int i = 0; i < list.size(); i++) {
                transform(strlist[i].begin(),strlist[i].end(),strlist[i].begin(),::tolower);
            }
        }
        for (int i = 0; i < list.size(); i++) {
            qDebug() << QString::fromStdString(strlist[i]);
        }
        bubsort_letter(strlist, arr, list.size());
        int k = list.size();
        for (int i = top; i <= bottom; i++) {
            for (int j = left; j <= right; j++) {
//                qDebug() << QString::fromStdString(strlist[k]);
                ui->tableWidget->setItem(i, j, new QTableWidgetItem(QString::fromStdString(str[arr[--k]])));
            }
        }
    }
}
